﻿/*
This library is free software; you can redistribute it and/or
modify it under the terms of the GNU Lesser General Public
License as published by the Free Software Foundation; either
version 2.1 of the License, or (at your option) any later version.

This library is distributed in the hope that it will be useful,
but WITHOUT ANY WARRANTY; without even the implied warranty of
MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the GNU
Lesser General Public License for more details.

You should have received a copy of the GNU Lesser General Public
License along with this library; if not, write to the Free Software
Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA  02110-1301  USA
*/

using System;
using System.Text;
using System.Collections.Generic;
using System.Linq;
using Microsoft.VisualStudio.TestTools.UnitTesting;
using System.Data.SqlServerCe;
using System.Data.Common;
using System.IO;
using IvyOrm.Test;
using System.Diagnostics;
using System.Data.SqlClient;
using System.Text.RegularExpressions;

namespace IvyOrm.Test
{
    [TestClass]
    [DeploymentItem("fbembed")] //FireBird
    [DeploymentItem("x86")] //SQLite
    public class DatabaseTestSuite
    {
        public class SimpleTable
        {
            [PrimaryKey(PrimaryKeyOptions.ReturnKeyValueOnInsert)]
            public int ColumnA { get; set; }
            public string ColumnB { get; set; }
        }
        public class StringTable
        {
            [PrimaryKey(PrimaryKeyOptions.ReturnKeyValueOnInsert)]
            public string ColumnA;
            [PrimaryKey(PrimaryKeyOptions.ReturnKeyValueOnInsert)]
            public string ColumnB;
            public string ColumnC;
            public string ColumnD;
        }

        [TestMethod]
        [TestCategory("SQL Server Compact Edition")]
        public void SqlCeConnection_TestSuite()
        {
            SqlCeConnectionStringBuilder builder = new SqlCeConnectionStringBuilder();
            builder.DataSource = Path.ChangeExtension(Path.GetRandomFileName(), ".sdf");

            SqlCeEngine engine = new SqlCeEngine(builder.ConnectionString);
            engine.CreateDatabase();
            using (var connection = new SqlCeConnection(builder.ConnectionString))
            {
                connection.Open();

                connection.ExecuteNonQuery(@"CREATE TABLE SimpleTable 
                              (
                                 ColumnA int IDENTITY(1,1) PRIMARY KEY, 
                                 ColumnB nvarchar(100)
                              )");
                connection.ExecuteNonQuery("create table StringTable (ColumnA nvarchar(200), ColumnB nvarchar(200), ColumnC nvarchar(200), ColumnD nvarchar(200))");
                executeAllTests(connection, supportsMultiplePrimaryKeys: false);
            }
        }

        [TestMethod]
        [TestCategory("SQLite")]
        [TestCategory("TestSuite")]
        public void SQLiteConnection_TestSuite()
        {
            using (var connection = new System.Data.SQLite.SQLiteConnection("Data Source=:memory:;Version=3;New=True;"))
            {
                connection.Open();
                connection.ExecuteNonQuery("create table SimpleTable (ColumnA INTEGER PRIMARY KEY, ColumnB varchar(200))");
                connection.ExecuteNonQuery("create table StringTable (ColumnA varchar(200), ColumnB varchar(200), ColumnC varchar(200), ColumnD varchar(200))");
                executeAllTests(connection, supportsMultiplePrimaryKeys: false);
            }
        }

        [TestMethod]
        [TestCategory("SQLite")]
        [TestCategory("TestSuite")]
        [DeploymentItem("sqlite3.dll")] //SQLite (required for Devart)
        public void Devart_SQLiteConnection_TestSuite()
        {
            using (var connection = new Devart.Data.SQLite.SQLiteConnection("Data Source=:memory:;Version=3;"))
            {
                connection.Open();
                connection.ExecuteNonQuery("create table SimpleTable (ColumnA INTEGER PRIMARY KEY, ColumnB varchar(200))");
                connection.ExecuteNonQuery("create table StringTable (ColumnA varchar(200), ColumnB varchar(200), ColumnC varchar(200), ColumnD varchar(200))");
                executeAllTests(connection, supportsMultiplePrimaryKeys: false);
            }
        }

        [TestMethod]
        [TestCategory("Firebird")]
        [TestCategory("TestSuite")]
        [DeploymentItem("fbembed.dll")] //Firebird
        [DeploymentItem("ib_util.dll")] //Firebird
        [DeploymentItem("icudt30.dll")] //Firebird
        [DeploymentItem("icuin30.dll")] //Firebird
        [DeploymentItem("icuuc30.dll")] //Firebird
        [DeploymentItem("firebird.msg")] //Firebird
        public void FbConnection_TestSuite()
        {
            var parameters = new FirebirdSql.Data.FirebirdClient.FbConnectionStringBuilder();
            parameters.Add("User", "SYSDBA");
            parameters.Add("Password", "password");
            parameters.Database = Path.GetFullPath(Path.ChangeExtension(Path.GetRandomFileName(), ".fdb"));
            parameters.ServerType = FirebirdSql.Data.FirebirdClient.FbServerType.Embedded;
            FirebirdSql.Data.FirebirdClient.FbConnection.CreateDatabase(parameters.ConnectionString);
            using (var connection = new FirebirdSql.Data.FirebirdClient.FbConnection(parameters.ConnectionString))
            {
                connection.Open();

                connection.ExecuteNonQuery("create table SimpleTable (ColumnA INTEGER PRIMARY KEY, ColumnB varchar(200))");
                connection.ExecuteNonQuery("CREATE GENERATOR gen_t1_id;");
                connection.ExecuteNonQuery("SET GENERATOR gen_t1_id TO 0;");
                connection.ExecuteNonQuery(
                            @"
                            CREATE TRIGGER T1_BI FOR SimpleTable
                            ACTIVE BEFORE INSERT POSITION 0
                            AS
                            BEGIN
                            if (NEW.ColumnA is NULL) then NEW.ColumnA = GEN_ID(GEN_T1_ID, 1);
                            END");

                connection.ExecuteNonQuery("create table StringTable (ColumnA varchar(200), ColumnB varchar(200), ColumnC varchar(200), ColumnD varchar(200))");

                executeAllTests(connection, supportsMultiplePrimaryKeys: false);
            }
        }

        [TestMethod]
        [TestCategory("SQL Server")]
        [TestCategory("Requires Server")]
        [TestCategory("TestSuite")]
        public void SqlConnection_TestSuite()
        {
            using (var connection = new SqlConnection(@"Data Source=DATABASES\SQLEXPRESS;User ID=sa;Password=P@ssword;Connection Timeout=2;"))
            {
                try
                {
                    connection.Open();
                }
                catch (Exception ex)
                {
                    Assert.Inconclusive("Failed to connect to database, " + ex.Message);
                }

                try
                {
                    connection.ExecuteNonQuery(@"IF OBJECT_ID('SimpleTable') IS NOT NULL DROP TABLE SimpleTable ");
                    connection.ExecuteNonQuery(@"IF OBJECT_ID('StringTable') IS NOT NULL DROP TABLE StringTable ");

                    connection.ExecuteNonQuery(@"CREATE TABLE SimpleTable 
                              (
                                 ColumnA int IDENTITY(1,1) PRIMARY KEY, 
                                 ColumnB nvarchar(100)
                              )");
                    connection.ExecuteNonQuery("create table StringTable (ColumnA nvarchar(200), ColumnB nvarchar(200), ColumnC nvarchar(200), ColumnD nvarchar(200))");
                    executeAllTests(connection, supportsMultiplePrimaryKeys: false);
                }
                finally
                {
                    connection.ExecuteNonQuery(@"IF OBJECT_ID('SimpleTable') IS NOT NULL DROP TABLE SimpleTable ");
                    connection.ExecuteNonQuery(@"IF OBJECT_ID('StringTable') IS NOT NULL DROP TABLE StringTable ");
                }
            }
        }

        [TestMethod]
        [TestCategory("PostgreSQL")]
        [TestCategory("Requires Server")]
        [TestCategory("TestSuite")]
        public void NpgsqlConnection_TestSuite()
        {
            using (var connection = new Npgsql.NpgsqlConnection("Server=Databases;Port=5432;Database=postgres;User Id=postgres;Password=password"))
            {
                try
                {
                    connection.Open();
                }
                catch (Exception ex)
                {
                    Assert.Inconclusive("Failed to connect to database, " + ex.Message);
                }

                try
                {
                    connection.ExecuteNonQuery("drop table if exists SimpleTable");
                    connection.ExecuteNonQuery("drop table if exists StringTable");
                    connection.ExecuteNonQuery("drop sequence if exists SimpleTable_Seq");

                    connection.ExecuteNonQuery("CREATE SEQUENCE SimpleTable_Seq;");
                    connection.ExecuteNonQuery("create table SimpleTable (ColumnA INTEGER PRIMARY KEY  DEFAULT NEXTVAL('SimpleTable_Seq'), ColumnB varchar(200))");
                    connection.ExecuteNonQuery("create table StringTable (ColumnA varchar(200), ColumnB varchar(200), ColumnC varchar(200), ColumnD varchar(200))");

                    executeAllTests(connection, supportsMultiplePrimaryKeys: true);
                }
                finally
                {
                    connection.ExecuteNonQuery("drop table if exists SimpleTable");
                    connection.ExecuteNonQuery("drop table if exists StringTable");
                    connection.ExecuteNonQuery("drop sequence if exists SimpleTable_Seq");
                }

            }
        }

        [TestMethod]
        [TestCategory("PostgreSQL")]
        [TestCategory("Requires Server")]
        [TestCategory("TestSuite")]
        public void Devart_PgSqlConnection_TestSuite()
        {
            using (var connection = new Devart.Data.PostgreSql.PgSqlConnection("Server=Databases;Port=5432;Database=postgres;User Id=postgres;Password=password"))
            {
                try
                {
                    connection.Open();
                }
                catch (Exception ex)
                {
                    Assert.Inconclusive("Failed to connect to database, " + ex.Message);
                }

                try
                {
                    connection.ExecuteNonQuery("drop table if exists SimpleTable");
                    connection.ExecuteNonQuery("drop table if exists StringTable");
                    connection.ExecuteNonQuery("drop sequence if exists SimpleTable_Seq");

                    connection.ExecuteNonQuery("CREATE SEQUENCE SimpleTable_Seq;");
                    connection.ExecuteNonQuery("create table SimpleTable (ColumnA INTEGER PRIMARY KEY  DEFAULT NEXTVAL('SimpleTable_Seq'), ColumnB varchar(200))");
                    connection.ExecuteNonQuery("create table StringTable (ColumnA varchar(200), ColumnB varchar(200), ColumnC varchar(200), ColumnD varchar(200))");

                    executeAllTests(connection, supportsMultiplePrimaryKeys: true);
                }
                finally
                {
                    connection.ExecuteNonQuery("drop table if exists SimpleTable");
                    connection.ExecuteNonQuery("drop table if exists StringTable");
                    connection.ExecuteNonQuery("drop sequence if exists SimpleTable_Seq");
                }
            }
        }

        [TestMethod]
        [TestCategory("Oracle")]
        [TestCategory("Requires Server")]
        [TestCategory("TestSuite")]
        public void OracleConnection_TestSuite()
        {
            using (var connection = new Oracle.DataAccess.Client.OracleConnection())
            {
                connection.ConnectionString = "Data Source=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=Databases)(PORT=1521)))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=XE)));User Id=system;Password=password;Connection Timeout=2;";
                try
                {
                    connection.Open();
                }
                catch (Exception ex)
                {
                    Assert.Inconclusive("Failed to connect to database, " + ex.Message);
                }

                try
                {
                    if (connection.ValueSingle<bool>("SELECT count(*) from user_tables where table_name='SIMPLETABLE'"))
                        connection.ExecuteNonQuery("drop table SIMPLETABLE cascade constraints");

                    if (connection.ValueSingle<bool>("SELECT count(*) from user_tables where table_name='STRINGTABLE'"))
                        connection.ExecuteNonQuery("drop table STRINGTABLE cascade constraints");

                    if (connection.ValueSingle<bool>("SELECT count(*) from user_sequences where sequence_name='SIMPLETABLE_SEQ'"))
                        connection.ExecuteNonQuery("drop sequence SimpleTable_Seq");

                    connection.ExecuteNonQuery("create sequence SimpleTable_Seq start with 1 increment by 1");


                    connection.ExecuteNonQuery("create table SimpleTable (ColumnA INTEGER PRIMARY KEY, ColumnB varchar(200))");
                    connection.ExecuteNonQuery(
                            @"create or replace trigger SimpleTable_Insert
                        before insert on SimpleTable
                        for each row
                        begin
                            select SimpleTable_Seq.nextval into :new.ColumnA from dual;
                        end;"
                    );

                    connection.ExecuteNonQuery("create table StringTable (ColumnA varchar(200), ColumnB varchar(200), ColumnC varchar(200), ColumnD varchar(200))");

                    executeAllTests(connection, supportsMultiplePrimaryKeys: true);
                }
                finally
                {

                    if (connection.ValueSingle<bool>("SELECT count(*) from user_tables where table_name='SIMPLETABLE'"))
                        connection.ExecuteNonQuery("drop table SIMPLETABLE cascade constraints");

                    if (connection.ValueSingle<bool>("SELECT count(*) from user_tables where table_name='SIMPLETABLE'"))
                        connection.ExecuteNonQuery("drop table SIMPLETABLE cascade constraints");

                    if (connection.ValueSingle<bool>("SELECT count(*) from user_sequences where sequence_name='STRINGTABLE'"))
                        connection.ExecuteNonQuery("drop sequence StringTable");
                }
            }
        }

        [TestMethod]
        [TestCategory("Oracle")]
        [TestCategory("Requires Server")]
        [TestCategory("TestSuite")]
        public void Devart_OracleConnection_TestSuite()
        {
            using (var connection = new Devart.Data.Oracle.OracleConnection())
            {
                connection.ConnectionString = "Data Source=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=Databases)(PORT=1521)))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=XE)));User Id=system;Password=password;";
                try
                {
                    connection.Open();
                }
                catch (Exception ex)
                {
                    Assert.Inconclusive("Failed to connect to database, " + ex.Message);
                }

                try
                {
                    if (connection.ValueSingle<bool>("SELECT count(*) from user_tables where table_name='SIMPLETABLE'"))
                        connection.ExecuteNonQuery("drop table SIMPLETABLE cascade constraints");

                    if (connection.ValueSingle<bool>("SELECT count(*) from user_tables where table_name='STRINGTABLE'"))
                        connection.ExecuteNonQuery("drop table STRINGTABLE cascade constraints");

                    if (connection.ValueSingle<bool>("SELECT count(*) from user_sequences where sequence_name='SIMPLETABLE_SEQ'"))
                        connection.ExecuteNonQuery("drop sequence SimpleTable_Seq");

                    connection.ExecuteNonQuery("create sequence SimpleTable_Seq start with 1 increment by 1");


                    connection.ExecuteNonQuery("create table SimpleTable (ColumnA INTEGER PRIMARY KEY, ColumnB varchar(200))");
                    connection.ExecuteNonQuery(
                            @"create or replace trigger SimpleTable_Insert
                        before insert on SimpleTable
                        for each row
                        begin
                            select SimpleTable_Seq.nextval into :new.ColumnA from dual;
                        end;"
                    );

                    connection.ExecuteNonQuery("create table StringTable (ColumnA varchar(200), ColumnB varchar(200), ColumnC varchar(200), ColumnD varchar(200))");

                    executeAllTests(connection, supportsMultiplePrimaryKeys: true);
                }
                finally
                {

                    if (connection.ValueSingle<bool>("SELECT count(*) from user_tables where table_name='SIMPLETABLE'"))
                        connection.ExecuteNonQuery("drop table SIMPLETABLE cascade constraints");

                    if (connection.ValueSingle<bool>("SELECT count(*) from user_tables where table_name='SIMPLETABLE'"))
                        connection.ExecuteNonQuery("drop table SIMPLETABLE cascade constraints");

                    if (connection.ValueSingle<bool>("SELECT count(*) from user_sequences where sequence_name='STRINGTABLE'"))
                        connection.ExecuteNonQuery("drop sequence StringTable");
                }
            }
        }

        [TestMethod]
        [TestCategory("MySQL")]
        [TestCategory("Requires Server")]
        [TestCategory("TestSuite")]
        public void MySQLConnection_TestSuite()
        {
            using (var connection = new MySql.Data.MySqlClient.MySqlConnection("Server=Databases;Port=3306;Database=test;Uid=root;Pwd=password;"))
            {
                try
                {
                    connection.Open();
                }
                catch (Exception ex)
                {
                    

                    Assert.Inconclusive("Failed to connect to database, " + ex.Message);
                }

                try
                {
                    connection.ExecuteNonQuery("drop table if exists SimpleTable");
                    connection.ExecuteNonQuery("drop table if exists StringTable");
                    connection.ExecuteNonQuery("create table SimpleTable (ColumnA INT NOT NULL AUTO_INCREMENT, ColumnB VARCHAR(200) null, PRIMARY KEY (ColumnA));");
                    connection.ExecuteNonQuery("create table StringTable (ColumnA varchar(200), ColumnB varchar(200), ColumnC varchar(200), ColumnD varchar(200))");

                    executeAllTests(connection, supportsMultiplePrimaryKeys: false);
                }
                finally
                {
                    connection.ExecuteNonQuery("drop table if exists SimpleTable");
                    connection.ExecuteNonQuery("drop table if exists StringTable");
                }
            }
        }

        [TestMethod]
        [TestCategory("MySQL")]
        [TestCategory("Requires Server")]
        [TestCategory("TestSuite")]
        public void Devart_MySQLConnection_TestSuite()
        {
            using (var connection = new Devart.Data.MySql.MySqlConnection("Server=Databases;Database=test;Uid=root;Pwd=password;"))
            {
                try
                {
                    connection.Open();
                }
                catch (Exception ex)
                {
                    Assert.Inconclusive("Failed to connect to database, " + ex.Message);
                }

                try
                {
                    connection.ExecuteNonQuery("drop table if exists SimpleTable");
                    connection.ExecuteNonQuery("drop table if exists StringTable");
                    connection.ExecuteNonQuery("create table SimpleTable (ColumnA INT NOT NULL AUTO_INCREMENT, ColumnB VARCHAR(200) null, PRIMARY KEY (ColumnA));");
                    connection.ExecuteNonQuery("create table StringTable (ColumnA varchar(200), ColumnB varchar(200), ColumnC varchar(200), ColumnD varchar(200))");

                    executeAllTests(connection, supportsMultiplePrimaryKeys: false);
                }
                finally
                {
                    connection.ExecuteNonQuery("drop table if exists SimpleTable");
                    connection.ExecuteNonQuery("drop table if exists StringTable");
                }
            }
        }


        void executeAllTests(DbConnection connection, bool supportsMultiplePrimaryKeys)
        {
            Debug.Assert(connection != null);
            executeDatabaseTests(connection, supportsMultiplePrimaryKeys);
            connection.ExecuteNonQuery("delete from SimpleTable");
            using (var transaction = connection.BeginTransaction())
            {
                executeDatabaseTests(transaction, supportsMultiplePrimaryKeys);
            }
        }

        void executeDatabaseTests(DbConnection connection, bool supportsMultiplePrimaryKeys)
        {
            //Base Line
            Assert.AreEqual(0, connection.ValueSingle<int>("select count(*) from SimpleTable"));

            #region Parameter Placeholder
            {
                var factory = connection.GetFactory();
                Debug.Assert(factory != null);
                var commandBuilder = factory.CreateCommandBuilder();
                Debug.Assert(commandBuilder != null);

                var parameterName = commandBuilder.GetParameterPlaceholder(1);
                Debug.Print(connection.GetType().Name + ".GetParameterPlaceholder(1) ==> " + parameterName);

                var targetPatern = @"^" +
                             @"[\:\@\$]" +
                             @"[A-Za-z]+" +
                             @"1" +
                             @"$";
                Assert.IsTrue(Regex.IsMatch(parameterName, targetPatern));
            }
            #endregion

            #region Insert
            //Insert w/ return primary key
            var row1 = new SimpleTable { ColumnB = "Row1" };
            connection.RecordInsert(row1);
            Assert.AreNotEqual(0, row1.ColumnA);
            Assert.AreEqual(row1.ColumnA, connection.ValueSingle<int>("select ColumnA from SimpleTable where ColumnB = @0", "Row1"));

            //Inserts Second Row
            var row2 = new SimpleTable { ColumnB = "Row2" };
            connection.RecordInsert(row2);
            Assert.AreNotEqual(row1.ColumnA, row2.ColumnB);

            //Multiple Primary Keys
            if (!supportsMultiplePrimaryKeys)
            {
                var obj = CompilerService.CreateInstanceFromCode(@"public class StringTable
                                                            {
                                                                [PrimaryKey(  PrimaryKeyOptions.ReturnKeyValueOnInsert)]
                                                                public string ColumnA;
                                                                [PrimaryKey(PrimaryKeyOptions.ReturnKeyValueOnInsert)]
                                                                public string ColumnB;
                                                                public string ColumnC;
                                                                public string ColumnD;
                                                            }");

                AssertB.ThrowsException<NotSupportedException>(() => connection.RecordInsert((object)obj), (e) => e.Message.Contains("9C609BC5"));
            }
            else
            {
                var obj = CompilerService.CreateInstanceFromCode(@"
                                                                    public class StringTable
                                                                    {
                                                                        [ExcludeColumn]
                                                                        public int ColumnA_Changes { get; private set; }
                                                                        string m_ColumnA;
                                                                        [PrimaryKey(PrimaryKeyOptions.ReturnKeyValueOnInsert | PrimaryKeyOptions.IncludeInInsert)]
                                                                        public string ColumnA
                                                                        {
                                                                            get { return m_ColumnA; }
                                                                            set { ColumnA_Changes++; m_ColumnA = value; }
                                                                        }

                                                                        [ExcludeColumn]
                                                                        public int ColumnB_Changes { get; private set; }
                                                                        string m_ColumnB;
                                                                        [PrimaryKey(PrimaryKeyOptions.ReturnKeyValueOnInsert | PrimaryKeyOptions.IncludeInInsert)]
                                                                        public string ColumnB
                                                                        {
                                                                            get { return m_ColumnB; }
                                                                            set { ColumnB_Changes++; m_ColumnB = value; }
                                                                        }

                                                                        public string ColumnC;
                                                                        public string ColumnD;
                                                                    }
                                                            ");
                obj.ColumnA = "ColumnA";
                obj.ColumnB = "ColumnB";

                connection.RecordInsert((object)obj);

                Assert.AreEqual(2, (object)obj.ColumnA_Changes);
                Assert.AreEqual(2, (object)obj.ColumnB_Changes);
                Assert.AreEqual("ColumnA", (object)obj.ColumnA);
                Assert.AreEqual("ColumnB", (object)obj.ColumnB);

            }
            #endregion

            #region Update
            {  //Update
                row1.ColumnB = "Row1b";
                connection.RecordUpdate(row1);
                Assert.AreEqual("Row1b", connection.ValueSingle<string>("select ColumnB from SimpleTable where ColumnA = @0", row1.ColumnA));

                //No Rows Updated (should throw exception
                var row0 = new SimpleTable();
                AssertB.ThrowsException<IvyOrmException>(() => connection.RecordUpdate(row0), (e) => e.Message.Contains("C006CBFD"));
            }

            { //multiple primary keys
                var obj = CompilerService.CreateInstanceFromCode(@"public class StringTable
                                                            {
                                                                [PrimaryKey()]
                                                                public string ColumnA;
                                                                [PrimaryKey()]
                                                                public string ColumnB;
                                                                public string ColumnC;
                                                                public string ColumnD;
                                                            }");
                connection.ExecuteNonQuery("delete from StringTable");
                connection.ExecuteNonQuery("insert into StringTable (ColumnA,ColumnB, ColumnC) values (@0,@1,@2)", "Row1.A", "Row1.B", "Row1.C");

                obj.ColumnA = "Row1.A";
                obj.ColumnB = "Row1.B";
                obj.ColumnC = "Row1.C2";

                connection.RecordUpdate((object)obj);

                Assert.AreEqual("Row1.C2", connection.ValueSingle<string>("select ColumnC from StringTable"));
            }

            { //null primary keys
                var obj = CompilerService.CreateInstanceFromCode(@"public class StringTable
                                                            {
                                                                [PrimaryKey]
                                                                public string ColumnA;
                                                                public string ColumnB;
                                                            }");
                connection.ExecuteNonQuery("delete from StringTable");
                connection.ExecuteNonQuery("insert into StringTable (ColumnA,ColumnB) values (@0,@1)", null, "Row1");

                obj.ColumnA = null;
                obj.ColumnB = "Row1b";

                connection.RecordUpdate((object)obj);

                Assert.AreEqual("Row1b", connection.ValueSingle<string>("select ColumnB from StringTable where ColumnA is null"));
            }
            #endregion
            {  //Delete
                row1.ColumnB = "Row1b";
                connection.RecordDelete(row1);
                Assert.AreEqual(0, connection.ValueSingle<int>("select count(*) from SimpleTable where ColumnA = @0", row1.ColumnA));


                //No Rows Deleted (should throw exception)
                var row0 = new SimpleTable();
                AssertB.ThrowsException<IvyOrmException>(() => connection.RecordDelete(row0), (e) => e.Message.Contains("E0538F8A"));
            }

            //Clears SimpleTable
            connection.ExecuteNonQuery("delete from SimpleTable");

            //Sets Up Test Data
            var row3 = new SimpleTable { ColumnB = "Row3" };
            var row4 = new SimpleTable { ColumnB = "Row4" };
            connection.RecordInsert(row3);
            connection.RecordInsert(row4);

            { //RecordQuery
                var records = connection.RecordQuery<SimpleTable>("select * from SimpleTable order by ColumnB");
                Assert.AreEqual(2, records.Length);
                Assert.AreEqual("Row3", records[0].ColumnB);
                Assert.AreEqual("Row4", records[1].ColumnB);
            }

            {//RecordSingle
                var record = connection.RecordSingle<SimpleTable>("select * from SimpleTable where ColumnB=@0", "Row3");
                Assert.AreEqual(record.ColumnB, "Row3");
                Assert.IsTrue(record.ColumnA != 0);

                //Non record
                AssertB.ThrowsException<IvyOrmException>(() => connection.RecordSingle<SimpleTable>("select * from SimpleTable where ColumnB=@0", "Row0"));
            }

            { //RecordSingleOrDefault
                var record = connection.RecordSingleOrDefault<SimpleTable>("select * from SimpleTable where ColumnB=@0", "Row3");
                Assert.AreEqual(record.ColumnB, "Row3");
                Assert.IsTrue(record.ColumnA != 0);

                var record2 = connection.RecordSingleOrDefault<SimpleTable>("select * from SimpleTable where ColumnB=@0", "Row0");
                Assert.AreEqual(null, record2);
            }



            //Execute Reader
            try
            {
                using (var reader = connection.ExecuteReader("select ColumnB from SimpleTable order by ColumnB"))
                {
                    Assert.AreEqual(true, reader.Read());
                    Assert.AreEqual("Row3", reader[0]);
                    Assert.AreEqual(true, reader.Read());
                    Assert.AreEqual("Row4", reader[0]);
                    Assert.AreEqual(false, reader.Read());
                }
            }
            catch (NotSupportedException e)
            {
                if (e.Message.Contains("02CB7395"))
                { /* ignore */ }
                else
                    throw;
            }

            //Execute Scalar
            Assert.AreEqual("Row3", connection.ExecuteScalar("select ColumnB from SimpleTable where ColumnA = @0", row3.ColumnA));

            {//ValueQuery
                var values = connection.ValueQuery<string>("select ColumnB from SimpleTable order by ColumnB");
                Debug.Assert(values.Length == 2 && values[0] == "Row3" && values[1] == "Row4");
            }

            {//ValueSingle
                var value3 = connection.ValueSingle<string>("select ColumnB from SimpleTable where ColumnB = @0", "Row3");
                Assert.AreEqual("Row3", value3);
                AssertB.ThrowsException<IvyOrmException>(() => connection.ValueSingle<string>("select ColumnB from SimpleTable where ColumnB = @0", "Row0"));
            }

            {//ValueSingleOrDefault
                var value0 = connection.ValueSingleOrDefault<string>("select ColumnB from SimpleTable where ColumnB = @0", "Row0");
                Assert.AreEqual(null, value0);
                var value3 = connection.ValueSingleOrDefault<string>("select ColumnB from SimpleTable where ColumnB = @0", "Row3");
                Assert.AreEqual("Row3", value3);
            }
        }
        void executeDatabaseTests(DbTransaction connection, bool supportsMultiplePrimaryKeys)
        {
            //Base Line
            Assert.AreEqual(0, connection.ValueSingle<int>("select count(*) from SimpleTable"));

            #region Insert
            //Insert w/ return primary key
            var row1 = new SimpleTable { ColumnB = "Row1" };
            connection.RecordInsert(row1);
            Assert.AreNotEqual(0, row1.ColumnA);
            Assert.AreEqual(row1.ColumnA, connection.ValueSingle<int>("select ColumnA from SimpleTable where ColumnB = @0", "Row1"));

            //Inserts Second Row
            var row2 = new SimpleTable { ColumnB = "Row2" };
            connection.RecordInsert(row2);
            Assert.AreNotEqual(row1.ColumnA, row2.ColumnB);

            //Multiple Primary Keys
            if (!supportsMultiplePrimaryKeys)
            {
                var obj = CompilerService.CreateInstanceFromCode(@"public class StringTable
                                                            {
                                                                [PrimaryKey(  PrimaryKeyOptions.ReturnKeyValueOnInsert)]
                                                                public string ColumnA;
                                                                [PrimaryKey(PrimaryKeyOptions.ReturnKeyValueOnInsert)]
                                                                public string ColumnB;
                                                                public string ColumnC;
                                                                public string ColumnD;
                                                            }");

                AssertB.ThrowsException<NotSupportedException>(() => connection.RecordInsert((object)obj), (e) => e.Message.Contains("9C609BC5"));
            }
            #endregion

            #region Update
            {  //Update
                row1.ColumnB = "Row1b";
                connection.RecordUpdate(row1);
                Assert.AreEqual("Row1b", connection.ValueSingle<string>("select ColumnB from SimpleTable where ColumnA = @0", row1.ColumnA));

                //No Rows Updated (should throw exception
                var row0 = new SimpleTable();
                AssertB.ThrowsException<IvyOrmException>(() => connection.RecordUpdate(row0), (e) => e.Message.Contains("C006CBFD"));
            }

            { //multiple primary keys
                var obj = CompilerService.CreateInstanceFromCode(@"public class StringTable
                                                            {
                                                                [PrimaryKey()]
                                                                public string ColumnA;
                                                                [PrimaryKey()]
                                                                public string ColumnB;
                                                                public string ColumnC;
                                                                public string ColumnD;
                                                            }");
                connection.ExecuteNonQuery("delete from StringTable");
                connection.ExecuteNonQuery("insert into StringTable (ColumnA,ColumnB, ColumnC) values (@0,@1,@2)", "Row1.A", "Row1.B", "Row1.C");

                obj.ColumnA = "Row1.A";
                obj.ColumnB = "Row1.B";
                obj.ColumnC = "Row1.C2";

                connection.RecordUpdate((object)obj);

                Assert.AreEqual("Row1.C2", connection.ValueSingle<string>("select ColumnC from StringTable"));
            }

            { //null primary keys
                var obj = CompilerService.CreateInstanceFromCode(@"public class StringTable
                                                            {
                                                                [PrimaryKey]
                                                                public string ColumnA;
                                                                public string ColumnB;
                                                            }");
                connection.ExecuteNonQuery("delete from StringTable");
                connection.ExecuteNonQuery("insert into StringTable (ColumnA,ColumnB) values (@0,@1)", null, "Row1");

                obj.ColumnA = null;
                obj.ColumnB = "Row1b";

                connection.RecordUpdate((object)obj);

                Assert.AreEqual("Row1b", connection.ValueSingle<string>("select ColumnB from StringTable where ColumnA is null"));
            }
            #endregion
            {  //Delete
                row1.ColumnB = "Row1b";
                connection.RecordDelete(row1);
                Assert.AreEqual(0, connection.ValueSingle<int>("select count(*) from SimpleTable where ColumnA = @0", row1.ColumnA));


                //No Rows Deleted (should throw exception)
                var row0 = new SimpleTable();
                AssertB.ThrowsException<IvyOrmException>(() => connection.RecordDelete(row0), (e) => e.Message.Contains("E0538F8A"));
            }

            //Clears SimpleTable
            connection.ExecuteNonQuery("delete from SimpleTable");

            //Sets Up Test Data
            var row3 = new SimpleTable { ColumnB = "Row3" };
            var row4 = new SimpleTable { ColumnB = "Row4" };
            connection.RecordInsert(row3);
            connection.RecordInsert(row4);

            { //RecordQuery
                var records = connection.RecordQuery<SimpleTable>("select * from SimpleTable order by ColumnB");
                Assert.AreEqual(2, records.Length);
                Assert.AreEqual("Row3", records[0].ColumnB);
                Assert.AreEqual("Row4", records[1].ColumnB);
            }

            {//RecordSingle
                var record = connection.RecordSingle<SimpleTable>("select * from SimpleTable where ColumnB=@0", "Row3");
                Assert.AreEqual(record.ColumnB, "Row3");
                Assert.IsTrue(record.ColumnA != 0);

                //Non record
                AssertB.ThrowsException<IvyOrmException>(() => connection.RecordSingle<SimpleTable>("select * from SimpleTable where ColumnB=@0", "Row0"));
            }

            { //RecordSingleOrDefault
                var record = connection.RecordSingleOrDefault<SimpleTable>("select * from SimpleTable where ColumnB=@0", "Row3");
                Assert.AreEqual(record.ColumnB, "Row3");
                Assert.IsTrue(record.ColumnA != 0);

                var record2 = connection.RecordSingleOrDefault<SimpleTable>("select * from SimpleTable where ColumnB=@0", "Row0");
                Assert.AreEqual(null, record2);
            }



            //Execute Reader
            try
            {
                using (var reader = connection.ExecuteReader("select ColumnB from SimpleTable order by ColumnB"))
                {
                    Assert.AreEqual(true, reader.Read());
                    Assert.AreEqual("Row3", reader[0]);
                    Assert.AreEqual(true, reader.Read());
                    Assert.AreEqual("Row4", reader[0]);
                    Assert.AreEqual(false, reader.Read());
                }
            }
            catch (NotSupportedException e)
            {
                if (e.Message.Contains("02CB7395"))
                { /* ignore */ }
                else
                    throw;
            }

            //Execute Scalar
            Assert.AreEqual("Row3", connection.ExecuteScalar("select ColumnB from SimpleTable where ColumnA = @0", row3.ColumnA));

            {//ValueQuery
                var values = connection.ValueQuery<string>("select ColumnB from SimpleTable order by ColumnB");
                Debug.Assert(values.Length == 2 && values[0] == "Row3" && values[1] == "Row4");
            }

            {//ValueSingle
                var value3 = connection.ValueSingle<string>("select ColumnB from SimpleTable where ColumnB = @0", "Row3");
                Assert.AreEqual("Row3", value3);
                AssertB.ThrowsException<IvyOrmException>(() => connection.ValueSingle<string>("select ColumnB from SimpleTable where ColumnB = @0", "Row0"));
            }

            {//ValueSingleOrDefault
                var value0 = connection.ValueSingleOrDefault<string>("select ColumnB from SimpleTable where ColumnB = @0", "Row0");
                Assert.AreEqual(null, value0);
                var value3 = connection.ValueSingleOrDefault<string>("select ColumnB from SimpleTable where ColumnB = @0", "Row3");
                Assert.AreEqual("Row3", value3);
            }
        }
    }
}
